use supermarket;

ALTER TABLE table_supermarket ADD PARTITION (dt='20240621') LOCATION '/home/flume/ods/20240621/ods_supermarket_inc'; 

insert overwrite table dwd_shelves select item_name,shelf_status from table_supermarket;
insert overwrite table dwd_user_AND_product select user_id,item_name from table_supermarket;
insert overwrite table dwd_name select item_name from table_supermarket;
insert overwrite table dwd_UNLike select user_id,item_name,popularity_level from table_supermarket;
insert overwrite table dwd_namesales select item_name,sold_quantity from table_supermarket;

insert overwrite table dim_user select user_id from table_supermarket;
insert overwrite table dim_item select item_id,item_name,stock_quantity,popularity_level,sold_quantity from table_supermarket;

INSERT OVERWRITE TABLE function1 
SELECT  
    name,  
    SUM(CASE WHEN status = 'U' THEN 1 ELSE 0 END) AS up_status_value,  
    SUM(CASE WHEN status = 'D' THEN 1 ELSE 0 END) AS down_status_value  
FROM  dwd_shelves
GROUP BY  name; 

INSERT OVERWRITE TABLE category 
SELECT
    name,
    CASE name
        WHEN 'Chewing' THEN  'food'
        WHEN 'Chewing3' THEN  'food'
        WHEN 'Drinks' THEN  'food'
        WHEN 'Drinks0' THEN  'food'
        WHEN 'Notebook' THEN  'stationery'
        WHEN 'Notebook2' THEN  'stationery'
        WHEN 'Pencil' THEN  'stationery'
        WHEN 'Pencil1' THEN  'stationery'
        WHEN 'Toothpaste ' THEN  'household_products'
        WHEN 'Toothpaste 4' THEN  'household_products'
        END AS category
FROM  dwd_shelves;

INSERT OVERWRITE TABLE function1_2
SELECT 
    category,
    COUNT(category)
FROM category
GROUP BY category;

